﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using LanguageInfo.Data.Helper;
using LanguageInfo.Common.Utilities;


namespace LanguageInfo.DAL
{
	/// <summary>
	/// 数据访问基类，提供公共的访问数据的方法，都为泛型
	/// </summary>
	public class DataBaseClass
	{

		/// <summary>
		/// 构造函数
		/// </summary>
		public DataBaseClass()
		{
            _db = DatabaseFactory.GetDatabase("SqlConnection");
		}


		#region Database
		Database _db;

		/// <summary>
		/// 返回单例模式的默认的(default connection)数据访问帮助器
		/// </summary>
		public Database db
		{
			get { return _db; }
			set { _db = value; }
		}
		#endregion

		#region Parameter method
		/// <summary>
		/// 给Sql命令添加输入参数
		/// </summary>
		/// <param name="cmd">Sql命令</param>
		/// <param name="name">参数名</param>
		/// <param name="value">参数值</param>
		protected void AddInParameter(DbCommand cmd, string name, object value)
		{
			DbParameter para;
			if (value == null)
				value = DBNull.Value;
			else
			{
				if (value is DateTime && DateTime.Parse(value.ToString()) == DateTime.MinValue)
					value = DBNull.Value;
			}
			para = db.DbProviderFactory.CreateParameter();
			para.ParameterName = name;
			para.Value = value;
			cmd.Parameters.Add(para);
		}

		/// <summary>
		/// 批量添加Sql命令的参数，参数是Sql语句中的以@开头的字符，参数值顺序为参数在Sql语句出现的顺序
		/// </summary>
		/// <param name="cmd">Sql命令</param>
		/// <param name="values">值列表</param>
		protected void SetParameters(DbCommand cmd, params object[] values)
		{
			
			MatchCollection mc;
			mc = Regex.Matches(cmd.CommandText, @"(@[^ \(]+?)(\,| |$|\))");
			for (int i = 0; i < mc.Count; i++)
			{
				AddInParameter(cmd, mc[i].Groups[1].Value, values[i]);
			}
		}
		#endregion

		#region Select method

		#region Loadrow
		/// <summary>
		/// 载入一行数据，即一个实体的实例，在具体的子类中实现
		/// </summary>
		/// <typeparam name="T">实体类型</typeparam>
		/// <param name="reader"></param>
		/// <param name="item">需要载入数据的实体</param>
		protected virtual void LoadRow<T>(IDataReader reader, ref T item) { }
		#endregion

		#region Getlist
		/// <summary>
		/// 获取数据列表
		/// </summary>
		/// <param name="cmd">Sql命令</param>
		/// <returns></returns>
		protected List<T> GetList<T>(DbCommand cmd) where T : new()
		{
			int recc = 0;
			return GetList<T>(cmd, ref recc);
		}

		/// <summary>
		/// 获取数据列表，并返回记录总数（如果有的话，默认为第二条语句的结果）
		/// </summary>
		/// <param name="cmd">Sql命令</param>
		/// <param name="recc">记录总数</param>
		/// <returns></returns>
		protected List<T> GetList<T>(DbCommand cmd, ref int recc) where T : new()
		{
			List<T> list = new List<T>();
			using (IDataReader reader = db.ExecuteReader(cmd))
			{
				while (reader.Read())
				{
					T item = new T();
					LoadRow<T>(reader, ref item);
					list.Add(item);
				}
				recc = list.Count;
				if (reader.NextResult())
				{
					if (reader.Read())
						recc = reader.GetInt32(0);
				}
				reader.Close();
			}
			return list;
		}
		#endregion

		#region GetObject
		/// <summary>
		/// 获取一行数据（一个实体）
		/// </summary>
		/// <typeparam name="T">实体类型</typeparam>
		/// <param name="cmd">Sql命令</param>
		/// <returns></returns>
		protected T GetItem<T>(DbCommand cmd) where T : new()
		{			
			T item = default(T);
			using (IDataReader reader = db.ExecuteReader(cmd))
			{
				if (reader.Read())
				{
					item = new T();
					LoadRow<T>(reader, ref item);
				}
				reader.Close();
			}
			return item;
		}
		#endregion

		#region Select
		/// <summary>
		/// 获取所有数据列表
		/// </summary>
		/// <typeparam name="T">实体类型</typeparam>
		/// <param name="sql">sql语句</param>
		/// <returns></returns>
		protected List<T> Select<T>(string sql) where T : new()
		{
			DbCommand cmd = db.GetSqlStringCommand(sql);
			return GetList<T>(cmd);
		}

		/// <summary>
		/// 根据条件获取数据列表
		/// </summary>
		/// <typeparam name="T">实体类型</typeparam>
		/// <param name="sql">sql语句</param>
		/// <param name="values">参数值列表</param>
		/// <returns></returns>
		protected List<T> Select<T>(string sql, params object[] values) where T : new()
		{
			DbCommand cmd = db.GetSqlStringCommand(sql);
			SetParameters(cmd, values);
			return GetList<T>(cmd);
		}

		/// <summary>
		/// 根据条件获取一行数据（一个实体）
		/// </summary>
		/// <typeparam name="T">实体类型</typeparam>
		/// <param name="sql">sql语句</param>
		/// <param name="values"></param>
		/// <returns></returns>
		protected T GetItem<T>(string sql, params object[] values) where T : new()
		{
			DbCommand cmd = db.GetSqlStringCommand(sql);
			SetParameters(cmd, values);
			return GetItem<T>(cmd);
		}

		/// <summary>
		/// 分面获取数据，并可添加条件，排序，top等
		/// </summary>
		/// <typeparam name="T">实体类型</typeparam>
		/// <param name="tableName">表/视图名</param>
		/// <param name="pageIndex">页索引</param>
		/// <param name="pageSize">页大小</param>
		/// <param name="orderby">排序</param>
		/// <param name="condition">查询条件</param>
		/// <param name="top">top</param>
		/// <param name="recc">输出总记录数</param>
		/// <returns></returns>
		protected List<T> Pager<T>(string tableName, int start, int limit, string orderby, string condition, int top, ref int recc) where T : new()
		{
			string sql;
			sql = "Pager";
			DbCommand cmd = db.GetStoredProcCommand(sql);
			db.AddInParameter(cmd, "tablename", DbType.String, tableName);
			db.AddInParameter(cmd, "fieldslist", DbType.String, "*");
			db.AddInParameter(cmd, "start", DbType.Int32, start);
			db.AddInParameter(cmd, "limit", DbType.Int32, limit);
			db.AddInParameter(cmd, "condition", DbType.String, condition);
			db.AddInParameter(cmd, "orderby", DbType.String, orderby);
			db.AddInParameter(cmd, "top", DbType.Int32, top);
			List<T> list = GetList<T>(cmd, ref recc);
			return list;
		}
		#endregion

		#endregion


		#region caching method

		#region select from cache
		protected List<T> SelectCached<T>(string CACHE_KEY, string sql) where T : new()
		{
			if (!CachingUtility.Contain(CACHE_KEY))
				CachingUtility.Set(CACHE_KEY, this.Select<T>(sql));

			return (List<T>)CachingUtility.Get(CACHE_KEY);
		}
		#endregion

		#region remove cache
		protected void RemoveCachedData<T>(string CACHE_KEY)
		{
			CachingUtility.Remove(CACHE_KEY);
		}
		#endregion
		#endregion
	}
}
